# -*- coding: utf-8 -*-
"""
@File:    base_dao.py
@Author:  jk.dong
@Desc:    数据库增改查基础模块
"""
import traceback
from docwork import logging
from docwork.utils import MysqlDB, OracleDB, DMsqlDB, PostgresqlDB
from docwork.utils.crypto import decrypt
from docwork.config import (RUN_CONF, db1_type, db1_address, db1_name,
                            db1_port, db1_username, db1_password, db1_schema)


class SingletonMeta(type):
    """
    单例元类，根据附件副数据库保持配置项来判断返回新实例还是现有实例
    """
    _instances = {}

    def __call__(cls, *args, **kwargs):
        if not RUN_CONF['import-setting'].get('save_attachment_second'):
            if cls not in cls._instances:
                cls._instances[cls] = super(SingletonMeta, cls).__call__(*args, **kwargs)
            return cls._instances[cls]
        else:
            return super(SingletonMeta, cls).__call__(*args, **kwargs)


class BaseDao(metaclass=SingletonMeta):
    """
    数据库操作基类，封装sql语句的增删改查操作
    notes: 后缀_info的函数中的数据库连接通过参数conn传入，其他的操作函数需要注意连接的释放
    """

    def __init__(self, type_: int = 2, username: str = '', password: str = '', address: str = '', dbname: str = '',
                 port: int = 3306):
        try:
            if RUN_CONF['db-setting'].get('db_encrypt'):
                password = decrypt(password, config_=RUN_CONF['run-setting'])
            if type_ == 1:
                self.DBUtil = OracleDB(username, password, address, dbname, port)
            elif type_ == 2:  # 默认使用MySQL数据库
                self.DBUtil = MysqlDB(username, password, address, dbname, port)
            elif type_ == 4:
                self.DBUtil = DMsqlDB(username, password, address, dbname, port)
            elif type_ == 3 or type_ == 5:
                self.DBUtil = PostgresqlDB(username, password, address, dbname, port)
            self.pool = self.DBUtil.get_pool()
            logging.info("====== 已建立数据库连接 ======")
        except Exception as err:
            logging.info(traceback.format_exc())
            raise RuntimeError(f"数据库初始化失败，Error: {err}")

    def get_conn(self):
        conn = None
        try:
            conn = self.DBUtil.get_conn(self.pool)
            if conn.cursor():
                pass
                # logging.info("数据库连接获取成功")
        except Exception as err:
            logging.error(f"数据库池连接获取失败，Error: {err}")
            # 数据库重连（目前只针对PG SQL数据库）
            logging.warning("psycopg2 reconnect")
            import psycopg2
            if RUN_CONF['db-setting'].get('db_encrypt'):
                pg_conn = psycopg2.connect(host=db1_address, user=db1_username,
                                           password=decrypt(db1_password, config_=RUN_CONF['run-setting']),
                                           dbname=db1_name, port=db1_port)
            else:
                pg_conn = psycopg2.connect(host=db1_address, user=db1_username, password=db1_password,
                                           dbname=db1_name, port=db1_port)
            if pg_conn:
                conn = pg_conn
            else:
                logging.error(f"数据库重连失败")
        return conn

    @staticmethod
    def sql_execute(cu, sql, params=None, args=None):
        if args:
            cu.setinputsizes(**args)
        if params:
            cu.execute(sql, params)
        else:
            cu.execute(sql)

    def get_info(self, conn, sql, params=None, args=None):
        try:
            logging.info(f"执行查询SQL:<{sql}>,参数:<{params}>")
            cu = self.DBUtil.get_cursor(conn)
            if db1_type == 4:
                schema_sql = f'SET SCHEMA "{db1_schema}";' if '-' in str(db1_schema) else f"SET SCHEMA {db1_schema}"
                cu.execute(schema_sql)
            if db1_type == 3 or db1_type == 5:
                schema_sql = f'SET search_path TO "{db1_schema}";'
                cu.execute(schema_sql)
            self.sql_execute(cu, sql, params, args)
            results = list(cu.fetchall())
            cu.close()
            return results
        except Exception as err:
            logging.info(traceback.format_exc())
            logging.error(f"SQL查询出现异常. Error: {err}")
            return []

    def update_info(self, conn, sql, params=None, args=None):
        try:
            if params:
                if isinstance(params, dict):
                    new_params = {}
                    for key, value in params.items():
                        if isinstance(value, bytes):
                            dict_ = {key: '******'}
                        else:
                            dict_ = {key: value}
                        new_params.update(dict_)
                    logging.info(f"执行更新SQL:<{sql}>,参数:<{new_params}>")
                else:
                    logging.info("sql语句的传数类型不为dict，暂时不做日志显示")
            else:
                logging.info(f"执行更新SQL:<{sql}>,参数:<{params}>")
            cu = self.DBUtil.get_cursor(conn)
            if db1_type == 4:
                schema_sql = f'SET SCHEMA "{db1_schema}";' if '-' in str(db1_schema) else f"SET SCHEMA {db1_schema}"
                cu.execute(schema_sql)
            if db1_type == 3 or db1_type == 5:
                schema_sql = f'SET search_path TO "{db1_schema}";'
                cu.execute(schema_sql)
            self.sql_execute(cu, sql, params, args)
            cu.close()
            return 1
        except Exception as err:
            logging.info(traceback.format_exc())
            logging.error(f"SQL更新或插入出现异常. Error: {err}")
            return -1

    def update(self, sql, params=None, args=None):
        logging.info(f"执行更新SQL:<{sql}>, 参数:<{params}>")
        try:
            if sql:
                conn = self.get_conn()
                cu = self.DBUtil.get_cursor(conn)
                if conn is None or cu is None:
                    logging.info("conn or cu is null")
                    return -1
                if db1_type == 4:
                    schema_sql = f'SET SCHEMA "{db1_schema}";' if '-' in str(db1_schema) else f"SET SCHEMA {db1_schema}"
                    cu.execute(schema_sql)
                if db1_type == 3 or db1_type == 5:
                    schema_sql = f'SET search_path TO "{db1_schema}";'
                    cu.execute(schema_sql)
                if args:
                    cu.setinputsizes(**args)
                try:
                    if params:
                        if isinstance(params, dict):
                            cu.execute(sql, params)
                        elif type(params) in (list, tuple):
                            cu.prepare(sql)
                            cu.executemany(None, params)
                    else:
                        cu.execute(sql)
                    conn.commit()
                    rowcount = cu.rowcount
                    logging.info(f"SQL生效行数:{rowcount}")
                except Exception as err:
                    logging.info(traceback.format_exc())
                    logging.error(f"SQL更新或插入出现异常. Error: {err}")
                    rowcount = -1
                if db1_type == 1:  # Oracle
                    self.DBUtil.release_resource(conn, cu, False, self.pool)
                else:
                    self.DBUtil.release_resource(conn, cu)
                return rowcount
            else:
                logging.warning("sql语句为空")
                return -1
        except Exception as err:
            logging.info(traceback.format_exc())
            logging.error(f'base_dao-->update func error: {err}')
            return -1

    def search(self, sql, params=None, cols_info=False, args=None, get_summary=False):
        logging.info(f"执行查询SQL:<{sql}>, 参数:<{params}>")
        try:
            if sql:
                conn = self.get_conn()
                cu = self.DBUtil.get_cursor(conn)
                if conn is None or cu is None:
                    logging.info("数据库连接或游标为空")
                    if cols_info:
                        return None, None
                    else:
                        return None
                if db1_type == 4:
                    schema_sql = f'SET SCHEMA "{db1_schema}";' if '-' in str(db1_schema) else f"SET SCHEMA {db1_schema}"
                    cu.execute(schema_sql)
                if db1_type == 3 or db1_type == 5:
                    schema_sql = f'SET search_path TO "{db1_schema}";'
                    cu.execute(schema_sql)
                if args:
                    cu.setinputsizes(**args)
                try:
                    if params:
                        cu.execute(sql, params)
                    else:
                        cu.execute(sql)

                    if db1_type == 1:
                        # Oracle数据库中对lob类型的数据转换
                        import cx_Oracle
                        results = []
                        fetchall_data = list(cu.fetchall())
                        for index in range(len(fetchall_data)):
                            origin_data_tuple = ()
                            results_tuple = fetchall_data[index]
                            for value in results_tuple:
                                if isinstance(value, cx_Oracle.LOB):
                                    origin_data_tuple += (value.read(),)
                                else:
                                    origin_data_tuple += (value,)
                            # logging.info(f"origin_data_tuple:{origin_data_tuple}")
                            results.append(origin_data_tuple)
                        # logging.info(results)
                    else:
                        results = list(cu.fetchall())

                    columns_name = []
                    if cols_info:
                        cols = cu.description
                        for i in cols:
                            columns_name.append(i[0])
                    if get_summary:
                        for i in range(len(results)):
                            results[i] = list(results[i])
                            # 不同数据库summary字段的返回类型不同
                            try:
                                results[i][columns_name.index('SUMMARY')] = results[i][columns_name.index('SUMMARY')].read()
                            except:
                                pass
                except Exception as err:
                    logging.info(traceback.format_exc())
                    logging.warning(f"SQL查询出现异常. Error:{err}")
                    if db1_type == 1:  # Oracle
                        self.DBUtil.release_resource(conn, cu, False, self.pool)
                    else:
                        self.DBUtil.release_resource(conn, cu)
                    if cols_info:
                        return None, None
                    else:
                        return None
                if db1_type == 1:  # Oracle
                    self.DBUtil.release_resource(conn, cu, False, self.pool)
                else:
                    self.DBUtil.release_resource(conn, cu)
                if cols_info:
                    return results, columns_name
                else:
                    return results
            else:
                logging.warning("sql语句为空")
                if cols_info:
                    return None, None
                else:
                    return None
        except Exception as err:
            logging.info(traceback.format_exc())
            logging.error(f'base_dao-->search func error: {err}')
            if cols_info:
                return None, None
            else:
                return None


# 匹配不同数据库的sql语句的字段引用
def fit_sql(data, dbtype=db1_type):
    if dbtype == 1:
        return ':' + data
    elif dbtype == 2 or db1_type == 3 or dbtype == 5:
        return '%(' + data + ')s'
    elif dbtype == 4:  # 达梦数据库无参数传递，需要将整个sql语句写完整，这里用format来格式化拼接
        # return "'%(" + data + ")s'"
        integer_words = ['sellsidedocid', 'customerid', 'docid', 'status', 'rawmaillogid',
                         'sendflag', 'objid', 'storetype', 'convertstatus', 'filetype',
                         'contentsize', 'doctypeid', 'inputid', 'secret', 'attachmentflag',
                         'brokerid']
        if data in integer_words:
            return '{' + data + '}'
        else:
            return "'{" + data + "}'"


# 获取数据表的属性和对应值
def deal_date(data):
    res = []
    if data and data[0]:
        properties = [i.upper() for i in data[1]]
        for i in range(len(data[0])):
            res.append(dict(zip(properties, data[0][i])))
    return res
